CREATE OR REPLACE PROCEDURE "SYNC_GCXX"(O_MSG        OUT VARCHAR2) AS
  O_SQLERRM    VARCHAR2(1000);
  O_SQLCODE    VARCHAR2(50);
  V_COUNT NUMBER;
  V_MAX_CLXXBH NUMBER;
  V_RESULT NUMBER;
  V_JKSL NUMBER;
BEGIN
  V_RESULT := 0;
  SELECT MAX(CLXXBH) INTO V_MAX_CLXXBH FROM WX_GCXX;
  IF V_MAX_CLXXBH IS NULL THEN
     V_MAX_CLXXBH := 0;
  END IF;

  SELECT COUNT(1) INTO V_COUNT FROM V_GCXX WHERE JGSK >= SYSDATE-30 AND CLXXBH > V_MAX_CLXXBH;
  IF V_COUNT > 0 THEN
     FOR V_DATA IN (
          SELECT * FROM (
            SELECT T.*, ROWNUM AS NO  FROM (
                   SELECT CLXXBH,KKBH,KKMC,JGSK,CDBH,HPHM,HPYS,XSFXDM,XSFX,CLLS,CSYS,HPZL,CLSD,HPTP,QJTP
                FROM V_GCXX WHERE JGSK >= SYSDATE-30 AND CLXXBH > V_MAX_CLXXBH ORDER BY CLXXBH ASC) T) WHERE NO <= 500)
     LOOP
         V_RESULT := V_RESULT + 1;
         INSERT INTO WX_GCXX (CLXXBH,KKBH,KKMC,JGSK,CDBH,HPHM,HPYS,XSFXDM,XSFX,CLLS,CSYS,HPZL,CLSD,HPTP,QJTP,CREATE_DATE,MODIFY_DATE,JGRQ,JGSJ)
                VALUES(V_DATA.CLXXBH, V_DATA.KKBH, V_DATA.KKMC, V_DATA.JGSK, V_DATA.CDBH,
                V_DATA.HPHM, V_DATA.HPYS, V_DATA.XSFXDM, V_DATA.XSFX, V_DATA.CLLS,
                V_DATA.CSYS, V_DATA.HPZL, V_DATA.CLSD, V_DATA.HPTP, V_DATA.QJTP, SYSDATE, SYSDATE,TO_CHAR(V_DATA.JGSK,'YYYY-MM-DD'),TO_CHAR(V_DATA.JGSK,'HH24:MI:SS'));

         --对每条数据进行监控设置表的过滤
         FOR V_JKSZ IN (SELECT JKDH,KSSJ,JSSJ,JKTS,CLLS,GCCS FROM WX_JKSZ)
         LOOP
              IF V_DATA.CLLS = V_JKSZ.CLLS
                 AND TO_CHAR(V_DATA.JGSK,'yyyy-MM-dd')>=to_char(SYSDATE-V_JKSZ.JKTS,'yyyy-MM-dd')
                 AND TO_CHAR(V_DATA.JGSK,'yyyy-MM-dd')<=to_char(SYSDATE ,'yyyy-MM-dd')
                 AND TO_CHAR(V_DATA.JGSK,'HH24:MI:SS')>=V_JKSZ.KSSJ
                 AND TO_CHAR(V_DATA.JGSK,'HH24:MI:SS')<= V_JKSZ.JSSJ THEN
                SELECT COUNT(*) INTO V_JKSL FROM WX_GCXX WHERE
                       JGRQ>=to_char(SYSDATE-V_JKSZ.JKTS,'yyyy-MM-dd')
                       AND JGRQ<=to_char(SYSDATE ,'yyyy-MM-dd')
                       AND JGSJ>=V_JKSZ.KSSJ
                       AND JGSJ<=V_JKSZ.JSSJ
                       AND CLLS=V_JKSZ.CLLS
                       AND HPHM=V_DATA.HPHM;

                IF V_JKSL >= V_JKSZ.GCCS THEN
                   INSERT INTO WX_WFCL(CLXXBH,KKBH,KKMC,JGSK,CDBH,HPHM,HPYS,XSFXDM,XSFX,CLLS,CSYS,
                   HPZL,CLSD,HPTP,QJTP,JKDH,WFZT,CREATE_DATE) VALUES (V_DATA.CLXXBH,V_DATA.KKBH,
                   V_DATA.KKMC,V_DATA.JGSK,V_DATA.CDBH,V_DATA.HPHM, V_DATA.HPYS, V_DATA.XSFXDM,V_DATA.XSFX, V_DATA.CLLS,
                  V_DATA.CSYS, V_DATA.HPZL, V_DATA.CLSD, V_DATA.HPTP, V_DATA.QJTP,V_JKSZ.JKDH,0,SYSDATE);

                END IF;
              END IF;
         END LOOP;

     END LOOP;
  END IF;

  INSERT INTO WX_BATCH(BATCH,QTY,MAX_ID) VALUES(BATCH_SEQUENCE.NEXTVAL,V_RESULT,V_MAX_CLXXBH);

  O_MSG := '同步过车信息成功,共' || V_RESULT ||'条!';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    O_SQLERRM := SUBSTRB(SQLERRM, 1, 500);
    O_SQLCODE := SQLCODE;
    SELECT '同步过车信息失败!原因：' || O_SQLCODE || O_SQLERRM
      INTO O_MSG
      FROM DUAL;
END SYNC_GCXX;






CREATE OR REPLACE PROCEDURE "SYNC_CDFX"(O_MSG        OUT VARCHAR2) AS
  O_SQLERRM    VARCHAR2(1000);
  O_SQLCODE    VARCHAR2(50);
  V_COUNT NUMBER;
  V_MAX_KKID NUMBER;
  V_RESULT NUMBER;
BEGIN
  V_RESULT := 0;
  SELECT MAX(KKID) INTO V_MAX_KKID FROM WX_CDFX;
  IF V_MAX_KKID IS NULL THEN
     V_MAX_KKID := 0;
  END IF;

  SELECT COUNT(1) INTO V_COUNT FROM V_CDFX WHERE KKID > V_MAX_KKID;
  IF V_COUNT > 0 THEN
     FOR V_DATA IN (
      SELECT * FROM (
            SELECT T.*, ROWNUM AS NO  FROM (
                   SELECT KKID,KKBH,CDBH,WD,JD,FXDM,FXMC FROM V_CDFX
                   WHERE KKID > V_MAX_KKID ORDER BY KKID ASC) T) WHERE NO <= 500)
     LOOP
         V_RESULT := V_RESULT + 1;
         INSERT INTO WX_CDFX (KKID,KKBH,CDBH,WD,JD,FXDM,FXMC,CREATE_DATE,MODIFY_DATE)
                VALUES(V_DATA.KKID, V_DATA.KKBH, V_DATA.CDBH, V_DATA.WD, V_DATA.JD,
                V_DATA.FXDM, V_DATA.FXMC, SYSDATE, SYSDATE);
     END LOOP;
  END IF;

 INSERT INTO WX_BATCH(BATCH,QTY,MAX_ID) VALUES(BATCH_SEQUENCE.NEXTVAL,V_RESULT,V_MAX_KKID);
  O_MSG := '同步车道方向信息成功,共' || V_RESULT ||'条!';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    O_SQLERRM := SUBSTRB(SQLERRM, 1, 500);
    O_SQLCODE := SQLCODE;
    SELECT '同步车道方向信息失败!原因：' || O_SQLCODE || O_SQLERRM
      INTO O_MSG
      FROM DUAL;
END SYNC_CDFX;



CREATE OR REPLACE PROCEDURE "SYNC_KKXX"(O_MSG        OUT VARCHAR2) AS
  O_SQLERRM    VARCHAR2(1000);
  O_SQLCODE    VARCHAR2(50);
  V_COUNT NUMBER;
  V_MAX_KKID NUMBER;
  V_RESULT NUMBER;
BEGIN
  V_RESULT := 0;
  SELECT MAX(KKID) INTO V_MAX_KKID FROM WX_KKXX;
  IF V_MAX_KKID IS NULL THEN
     V_MAX_KKID := 0;
  END IF;

  SELECT COUNT(1) INTO V_COUNT FROM V_KKXX WHERE KKID > V_MAX_KKID;
  IF V_COUNT > 0 THEN
     FOR V_DATA IN (
         SELECT * FROM (
            SELECT T.*, ROWNUM AS NO  FROM (
                   SELECT KKID,KKBH,KKMC,WD,JD FROM V_KKXX WHERE KKID > V_MAX_KKID ORDER by KKID ASC)  T) WHERE NO <= 500)
     LOOP
         V_RESULT := V_RESULT + 1;
         INSERT INTO WX_KKXX (KKID,KKBH,KKMC,WD,JD,CREATE_DATE,MODIFY_DATE)
                VALUES(V_DATA.KKID, V_DATA.KKBH, V_DATA.KKMC, V_DATA.WD, V_DATA.JD,SYSDATE,SYSDATE);
     END LOOP;
  END IF;

 INSERT INTO WX_BATCH(BATCH,QTY,MAX_ID) VALUES(BATCH_SEQUENCE.NEXTVAL,V_RESULT,V_MAX_KKID);
  O_MSG := '同步卡口信息成功,共' || V_RESULT ||'条!';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    O_SQLERRM := SUBSTRB(SQLERRM, 1, 500);
    O_SQLCODE := SQLCODE;
    SELECT '同步卡口信息失败!原因：' || O_SQLCODE || O_SQLERRM
      INTO O_MSG
      FROM DUAL;
END SYNC_KKXX;




CREATE OR REPLACE PROCEDURE "SYNC_WFXX"(O_MSG        OUT VARCHAR2) AS
  O_SQLERRM    VARCHAR2(1000);
  O_SQLCODE    VARCHAR2(50);
  V_COUNT NUMBER;
  V_MAX_CLXXBH NUMBER;
  V_RESULT NUMBER;
BEGIN
  V_RESULT := 0;
  SELECT MAX(CLXXBH) INTO V_MAX_CLXXBH FROM WX_WFXX;
  IF V_MAX_CLXXBH IS NULL THEN
     V_MAX_CLXXBH := 0;
  END IF;

  SELECT COUNT(1) INTO V_COUNT FROM V_WFXX WHERE JGSK >= SYSDATE-30 AND CLXXBH > V_MAX_CLXXBH;
  IF V_COUNT > 0 THEN
     FOR V_DATA IN (
         SELECT * FROM (
                SELECT T.*, ROWNUM AS NO  FROM (
                       SELECT CLXXBH,KKBH,KKMC,JGSK,CDBH,HPHM,HPYS,XSFXDM,XSFX,CLLS,CSYS,HPZL,CLSD,WFXW,
                       HPTP,QJTP FROM V_WFXX
                       WHERE JGSK >= SYSDATE-30 AND CLXXBH > V_MAX_CLXXBH ORDER BY CLXXBH ASC) T) WHERE NO <= 500)
     LOOP
         V_RESULT := V_RESULT +1;
         INSERT INTO WX_WFXX (CLXXBH,KKBH,KKMC,JGSK,CDBH,HPHM,HPYS,XSFXDM,XSFX,CLLS,CSYS,HPZL,CLSD,WFXW,HPTP,QJTP,CREATE_DATE,MODIFY_DATE)
                VALUES(V_DATA.CLXXBH, V_DATA.KKBH, V_DATA.KKMC, V_DATA.JGSK, V_DATA.CDBH,
                V_DATA.HPHM, V_DATA.HPYS, V_DATA.XSFXDM, V_DATA.XSFX, V_DATA.CLLS,
                V_DATA.CSYS, V_DATA.HPZL, V_DATA.CLSD, V_DATA.WFXW, V_DATA.HPTP, V_DATA.QJTP,SYSDATE,SYSDATE);
     END LOOP;
  END IF;

 INSERT INTO WX_BATCH(BATCH,QTY,MAX_ID) VALUES(BATCH_SEQUENCE.NEXTVAL,V_RESULT,V_MAX_CLXXBH);
  O_MSG := '同步违法过车信息成功,共' || V_RESULT ||'条!';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    O_SQLERRM := SUBSTRB(SQLERRM, 1, 500);
    O_SQLCODE := SQLCODE;
    SELECT '同步违法过车信息失败!原因：' || O_SQLCODE || O_SQLERRM
      INTO O_MSG
      FROM DUAL;
END SYNC_WFXX;

